package queries;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;

import entitys.Customer;
import entitys.MarketingPattern;
import entitys.Product;

public class GetPatternedClientsQuery {

	
	public static ArrayList<Customer> exe(Connection conn) {
		ArrayList<Customer> arr = new ArrayList<Customer>();
		MarketingPattern mp;
		Customer cstm;
		Date date;
		String splitAreas[];
		String area;
		ResultSet res = null; 
		Statement stmt;
		
			
			try {
				stmt = conn.createStatement();
				
				res = stmt.executeQuery("SELECT * from ccrm.marketingpattern; ");
				res.next();
				mp=new MarketingPattern(res.getInt(2),res.getInt(3),res.getInt(4),res.getString(5).toString(),res.getInt(6),res.getInt(7),res.getInt(8),res.getString(9).toString());
				mp.setPatternID(res.getInt(1));
				area=mp.getAreas();
				splitAreas=area.split(";");
				
				
				res = stmt.executeQuery("SELECT * from ccrm.customer where permit=1 AND sagnificance>="+mp.getSignificance()+"; ");
			
			
				while(res.next())
				{
					Boolean shouldAdd=true,areaFound=false;
					cstm = new Customer(res.getString(2), res.getString(3), res.getString(6), res.getString(4), res.getString(5), res.getString(1), res.getInt(7)==1,res.getString(8));
					
					for (int i=0;i<splitAreas.length;i++)
					{
						if (cstm.getAddress().equals(splitAreas[i])) 
							areaFound=true;
					}
					shouldAdd=areaFound;
				
					Calendar dob = Calendar.getInstance();  
					dob.setTime(res.getDate(8)); 
					Calendar today = Calendar.getInstance();  
					int age = today.get(Calendar.YEAR) - dob.get(Calendar.YEAR);  
					if (today.get(Calendar.MONTH) < dob.get(Calendar.MONTH)) {
					  age--;  
					} else if (today.get(Calendar.MONTH) == dob.get(Calendar.MONTH)
					    && today.get(Calendar.DAY_OF_MONTH) < dob.get(Calendar.DAY_OF_MONTH)) {
					  age--;  
					}
					
					if (mp.getFromAge()<mp.getToAge())
						if (age>mp.getFromAge() && age<mp.getToAge())
							shouldAdd=true;
						else
							shouldAdd=false;
					else
						if (age<mp.getFromAge() && age>mp.getToAge())
							shouldAdd=true;
						else
							shouldAdd=false;
							
					if (shouldAdd&&areaFound)
					
					arr.add(cstm);
				
			
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		return arr;
		
	}
	
	public static ArrayList<Customer> exe(Connection conn,String str) { //with sector and type condition
		ArrayList<Customer> arr = new ArrayList<Customer>();
		MarketingPattern mp;
		Customer cstm;
		Date date;
		String splitAreas[];
		String area;
		ResultSet res = null; 
		Statement stmt;
		int patternID = Integer.parseInt(str.substring(1, str.length()));
			
			try {
				stmt = conn.createStatement();
				
				res = stmt.executeQuery("SELECT * from ccrm.marketingpattern WHERE patternID="+patternID+"; ");
				res.next();
				mp=new MarketingPattern(res.getInt(2),res.getInt(3),res.getInt(4),res.getString(5).toString(),res.getInt(6),res.getInt(7),res.getInt(8),res.getString(9).toString());
				mp.setPatternID(res.getInt(1));
				area=mp.getAreas();
				splitAreas=area.split(";");
				
				
				res = stmt.executeQuery("SELECT * from ccrm.customer where permit=1 AND sagnificance>="+mp.getSignificance()+"; ");
			
			
				while(res.next())
				{
					Boolean shouldAdd=true,areaFound=false;
					cstm = new Customer(res.getString(2), res.getString(3), res.getString(6), res.getString(4), res.getString(5), res.getString(1), res.getInt(7)==1,res.getString(8));
					
					for (int i=0;i<splitAreas.length;i++)
					{
						if (cstm.getAddress().equals(splitAreas[i])) 
							areaFound=true;
					}
					shouldAdd=areaFound;
				
					Calendar dob = Calendar.getInstance();  
					dob.setTime(res.getDate(8)); 
					Calendar today = Calendar.getInstance();  
					int age = today.get(Calendar.YEAR) - dob.get(Calendar.YEAR);  
					if (today.get(Calendar.MONTH) < dob.get(Calendar.MONTH)) {
					  age--;  
					} else if (today.get(Calendar.MONTH) == dob.get(Calendar.MONTH)
					    && today.get(Calendar.DAY_OF_MONTH) < dob.get(Calendar.DAY_OF_MONTH)) {
					  age--;  
					}
					
					if (mp.getFromAge()<mp.getToAge())
						if (age>mp.getFromAge() && age<mp.getToAge())
							shouldAdd=true;
						else
							shouldAdd=false;
					else
						if (age<mp.getFromAge() && age>mp.getToAge())
							shouldAdd=true;
						else
							shouldAdd=false;
							
					if (shouldAdd&&areaFound)
					
					arr.add(cstm);
				
			
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		return arr;
	}
}
